﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Public;

namespace DAL
{
    public class SoHoKhauDAL
    {
        public static List<SoHoKhauPublic> LoadSoHoKhau()
        {
            var sohokhau = Common.db.ExecuteStoreQuery<SoHoKhauPublic>(@"select * from SoHoKhau");
            return sohokhau.ToList();
        }

        public static SoHoKhauPublic LoadSoHoKhauBySoSHK(int SoSHK)
        {
            string sql = @"select * from SoHoKhau where SoSoHoKhau=" +SoSHK;
            var sohokhau = Common.db.ExecuteStoreQuery<SoHoKhauPublic>(sql);
          //  if sohokhau == DBNull
            return sohokhau.FirstOrDefault();
        }
        public static void InsertSoHoKhau(SoHoKhauPublic _shk)
        {
            SoHoKhau shk = new SoHoKhau();
            shk.MaSHK = _shk.MaSHK;
            shk.SoSoHoKhau = _shk.SoSoHoKhau;
            shk.SoNha = _shk.SoNha;
            shk.Duong = _shk.Duong;
            shk.KhuPho = _shk.KhuPho;
            shk.CanBoDangKi = _shk.CanBoDangKi;
            shk.ChucVuNguoiCap = _shk.ChucVuNguoiCap;
            shk.NgayCap = _shk.NgayCap;
            shk.NguoiCap = _shk.NguoiCap;
            Common.db.AddToSoHoKhaus(shk);
            Common.db.SaveChanges();
        }
        public static int LoadMaSHK()
        {            
            //var shk = Common.db.ExecuteStoreQuery<SoHoKhauPublic>(@"select Max(MaSHK) as MaSHK from SoHoKhau ");

            var shk = Common.db.ExecuteStoreQuery<int>(@"select ISNULL(Max(MaSHK),0) as MaSHK from SoHoKhau");
            return shk.ToList()[0];
        }
        public static SoHoKhauPublic LoadSoHoKhauByMaSHK(int MaSHK)
        {
            var shk = Common.db.ExecuteStoreQuery<SoHoKhauPublic>(@"select * from SoHoKhau where MaSHK=" + MaSHK);
            return shk.FirstOrDefault();
        }
        public static int CountSoHoKhau()
        {
            var shk = Common.db.ExecuteStoreQuery<int>(@"select count(MaSHK) from SoHoKhau");
            return shk.FirstOrDefault();
        }
        public static int LoadSoSoHoKhau()
        {
            var shk = Common.db.ExecuteStoreQuery<int>(@"select ISNULL(Max(SoSHK),0) as SoSHK from SoHoKhau");
            return shk.ToList()[0];
        }
        public static int CoutSoHoKhauByYear(int firstyear,int lastyear)
        {
            var shk = Common.db.ExecuteStoreQuery<int>(@"select count(MaSHK) from SoHoKhau where YEAR(NgayCap)>=" + firstyear + "and YEAR(NgayCap)<=" + lastyear);
            return shk.FirstOrDefault();
        }
    }
}
